package cn.edu.hcnu.dao.impl;

import cn.edu.hcnu.dao.IEmpDao;
import cn.edu.hcnu.model.EmpDao;
import cn.edu.hcnu.unitl.PageModel;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmpDaoImpl implements IEmpDao {

    /**
     * 查询viewEmp(String page)
     * @param page
     * @return pm 或者null
     */
    @Override
    public PageModel viewEmp(String page) {
        int currentPage=0;
        int pageSize=3;
        if(page==null){
            currentPage=1;
        }else {
            currentPage=Integer.parseInt(page);
        }
        int startRow=(currentPage-1)*pageSize+1;
        int endRow=currentPage*pageSize;

        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="scott";
        String password="tiger";


        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="select * from (select rownum rn,t.* from (select * from emp) t where rownum<=? ) where rn>=?";
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection(url,username,password);
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,endRow);
            pstmt.setInt(2,startRow);
            rs=pstmt.executeQuery();
            List emplist=new ArrayList();
            while (rs.next()){
                EmpDao emp=new EmpDao();
                emp.setEmpno(rs.getInt("EMPNO"));
                emp.setEname(rs.getString("ENAME"));
                emp.setJob(rs.getString("JOB"));
                emp.setMgr(rs.getInt("MGR"));
                emp.setHiredate(rs.getDate("HIREDATE"));
                emp.setSal(rs.getFloat("SAL"));
                emp.setComm(rs.getFloat("COMM"));
                emp.setDeptno(rs.getInt("DEPTNO"));
                emplist.add(emp);
            }

//            request.getRequestDispatcher("viewEmp.jsp").forward(request,response);
            //跳转jsp前先查询总数
            sql="select  count(*) from emp";
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            rs.next();
            int rowCount=rs.getInt(1);
            int totalPage=rowCount%pageSize==0?(rowCount/pageSize):(rowCount/pageSize)+1;

            System.out.println("currentPage后端"+currentPage);
            System.out.println("totalPage后端"+totalPage);

            PageModel pm=new PageModel();
            pm.setList(emplist);
            pm.setCurrentPage(currentPage);
            pm.setTotalPage(totalPage);

            return  pm;
   /*         //上一页
            if(currentPage>1){
                request.setAttribute("PrePage",currentPage-1);
            }else {
                request.setAttribute("PrePage",currentPage);
            }

            //下一页nextPage
            if(currentPage<totalPage){
                request.setAttribute("nextPage",currentPage+1);
            }else {
                request.setAttribute("nextPage",currentPage);
            }
*/


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        return  null;

    }

    /**
     * 删除deleteEmp(String empno)
     * @param empno
     * @return
     */
    @Override
    public PageModel deleteEmp(String empno) {

        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="scott";
        String password="tiger";


        Connection conn=null;
        PreparedStatement pstmt=null;
        String sql="delete from emp where empno=?";
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection(url,username,password);
            conn.setAutoCommit(false);
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,Integer.parseInt(empno));
            pstmt.executeUpdate();
            conn.commit();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 添加addEmp(String empno, String empname, String hiredate)
     * @param empno
     * @param empname
     * @param hiredate
     * @return
     */
    @Override
    public PageModel addEmp(String empno, String empname, String hiredate) {

        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="scott";
        String password="tiger";

        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="INSERT  INTO emp(empno,ename,hiredate) VALUES (?,?,?)";

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection(url,username,password);

            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,Integer.parseInt(empno));
            pstmt.setString(2,empname);
            pstmt.setDate(3,java.sql.Date.valueOf(hiredate));
            pstmt.executeUpdate();
            conn.commit();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    /**
     * 更新updateEmpaddEmp(String empno)
     * @param empno
     * @return
     */
    @Override
    public PageModel updateEmpaddEmp(String empno) {
        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="scott";
        String password="tiger";

        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="SELECT * FROM emp WHERE empno=?";
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection(url,username,password);
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,Integer.parseInt(empno));
            rs=pstmt.executeQuery();

            List<EmpDao> emplist=new ArrayList();
            while (rs.next()){
                EmpDao emp=new EmpDao();
                emp.setEmpno(rs.getInt("EMPNO"));
                emp.setEname(rs.getString("ENAME"));
                emp.setJob(rs.getString("JOB"));
                emp.setMgr(rs.getInt("MGR"));
                emp.setHiredate(rs.getDate("HIREDATE"));
                emp.setSal(rs.getFloat("SAL"));
                emp.setComm(rs.getFloat("COMM"));
                emp.setDeptno(rs.getInt("DEPTNO"));
                emplist.add(emp);

            }
            PageModel pm =new PageModel();
            pm.setList(emplist);
            return  pm;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    /**
     * 修改modifyEmpaddEmp(String empno, String empname, String hiredate)
     * @param empno
     * @param empname
     * @param hiredate
     * @return
     */
    @Override
    public PageModel modifyEmpaddEmp(String empno, String empname, String hiredate) {
        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="scott";
        String password="tiger";

        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="UPDATE emp SET ename=?,hiredate=? WHERE  empno=?";

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection(url,username,password);

            pstmt=conn.prepareStatement(sql);

            pstmt.setString(1,empname);
            pstmt.setDate(2,java.sql.Date.valueOf(hiredate));
            pstmt.setInt(3,Integer.parseInt(empno));
            pstmt.executeUpdate();
            conn.commit();

            //request.getRequestDispatcher("index.jsp").forward(request,response);


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }
}
